package com.vteba.utils.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.vteba.lang.bytecode.MethodAccess;
import com.vteba.utils.json.Bean;
import com.vteba.utils.reflection.AsmUtils;

/**
 * 解析Excel数据为对象List
 * @author yinlei
 * @date 2012-6-25
 */
public class ImportExcel<T> {
	private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcel.class);
	
	private InputStream excelStream;
	private Class<T> beanType;
	private String[] columns;
	private String[] setterMethods;
	private boolean xlsx2007;
	private MethodAccess methodAccess;
	private Class<?>[] parameterClasses;
	
	/**
	 * 构造一个Excel导入工具类。后面需要调用setter方法设置相应的数据和导出列以及类型。
	 */
	public ImportExcel() {
	}
	
	/**
	 * 根据参数构造一个Excel导入工具类。
	 * @param excelStream Excel文件流
	 * @param beanType Excel中每一行所对应的JavaBean类型
	 * @param columns Excel中每一列所对应的JavaBean的属性名数组
	 */
	public ImportExcel(InputStream excelStream, Class<T> beanType, String[] columns) {
		this.excelStream = excelStream;
		this.beanType = beanType;
		this.columns = columns;
		this.methodAccess = AsmUtils.createMethodAccess(beanType);
		matchMethodParameter();
	}

	/**
	 * 根据参数构造一个Excel导入工具类。
	 * @param excelFile Excel文件
	 * @param beanType Excel中每一行所对应的JavaBean类型
	 * @param columns Excel中每一列所对应的JavaBean的属性名数组
	 */
	public ImportExcel(File excelFile, Class<T> beanType, String[] columns) {
		try {
			String fileName = excelFile.getName().toLowerCase();
			if (fileName.endsWith(".xlsx")) {
				xlsx2007 = true;
			} else if (!fileName.endsWith(".xls")) {// 如果不是以.xlsx结尾，那么一定是以.xls结尾，否则文件不对
				throw new IllegalArgumentException("文件扩展名不对，不是Excel文件。");
			}
			FileInputStream fis = new FileInputStream(excelFile);
			this.excelStream = fis;
		} catch (FileNotFoundException e) {
			LOGGER.error("没有找到文件。msg=[{}].", e.getMessage());
		}
		this.beanType = beanType;
		this.columns = columns;
		this.methodAccess = AsmUtils.createMethodAccess(beanType);
		matchMethodParameter();
	}
	
	/**
	 * 设置Excel文件流
	 * @param excelStream the excelStream
	 */
	public void setExcelStream(InputStream excelStream) {
		if (excelStream == null) {
			throw new NullPointerException("Excel文件流为空.");
		}
		this.excelStream = excelStream;
	}

	/**
	 * 设置Excel中每一行，导入成的JavaBean类型。
	 * @param beanType 泛型参数类型
	 */
	public void setBeanType(Class<T> beanType) {
		this.beanType = beanType;
		this.methodAccess = AsmUtils.createMethodAccess(beanType);
		if (columns != null) {
			matchMethodParameter();
		}
	}

	/**
	 * 设置Excel中每一列，顺序对应到的JavaBean的属性名数组
	 * @param columns Excel中对应到JavaBean的属性名数组
	 */
	public void setColumns(String[] columns) {
		this.columns = columns;
		if (methodAccess != null) {
			matchMethodParameter();
		}
	}

	/**
	 * 是否是Excel 2007
	 * @param xlsx2007 Excel 2007 true，other false
	 */
	public void setXlsx2007(boolean xlsx2007) {
		this.xlsx2007 = xlsx2007;
	}
	
	/**
	 * 构建setter方法名，以及属性名的类型。
	 */
	private void matchMethodParameter() {
		setterMethods = new String[columns.length];
		parameterClasses = new Class<?>[columns.length];
		Class<?>[][] parameterTypes = methodAccess.getParameterTypes();
		for (int i = 0; i < columns.length; i++) {
			setterMethods[i] = "set" + StringUtils.capitalize(columns[i]);
			int index = methodAccess.getIndex(setterMethods[i]);
			parameterClasses[i] = parameterTypes[index][0];
		}
	}
	
	/**
	 * 将Excel文件解析生成JavaBean List
	 * @return 解析后的JavaBean List
	 */
	public List<T> toList() {
		List<T> resultList = new ArrayList<T>();//要返回的数据
		Workbook wb = null;
		try {
			if (xlsx2007) {
				wb = new XSSFWorkbook(excelStream);
			} else {
				wb = new HSSFWorkbook(excelStream);
			}
			// 获得第一个sheet的内容
			Sheet sheet = wb.getSheetAt(0);
			int rowIndex = 1;// 行数，索引从0开始的。从第二行开始，第一行是标题
			for (Row row = sheet.getRow(rowIndex); row != null; row = sheet.getRow(++rowIndex)) {
				T model = beanType.newInstance();// 实例化类
				for (int i = 0; i < columns.length; i++) {// 处理每一列的内容
					Cell cell = row.getCell(i);
					if (cell != null) {
						switch (cell.getCellType()) {
						case Cell.CELL_TYPE_STRING:// 字符串类型
							methodAccess.invoke(model, setterMethods[i], cell.getStringCellValue());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							// 判断是否是日期类型，poi内部将日期也是使用double来存储的
							if (DateUtil.isCellDateFormatted(cell)) {
								methodAccess.invoke(model, setterMethods[i], cell.getDateCellValue());
							} else {// 数字类型，这里区分double，int，long
								Object temp = TypeConverter.convertValue(cell.getNumericCellValue(), parameterClasses[i]);
								methodAccess.invoke(model, setterMethods[i], temp);
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:// boolean类型
							methodAccess.invoke(model, setterMethods[i], cell.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_FORMULA:// 公式
							break;
						case Cell.CELL_TYPE_BLANK:// 空，不处理
							break;
						case Cell.CELL_TYPE_ERROR:// 错误
							break;
						default:
							
							break;
						}
					}
				}
				resultList.add(model);
			}
		} catch (IllegalAccessException e) {
			LOGGER.error("Excel导入实例化类，非法访问异常,msg=[{}].", e.getMessage());
		} catch (IOException e) {
			LOGGER.error("Excel导入IO异常,msg=[{}].", e.getMessage());
		} catch (InstantiationException e) {
			LOGGER.error("Excel导入实例化类异常,msg=[{}].", e.getMessage());
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					// ignore
				}
			}
		}
		return resultList;
	}
	
	public static void main(String[] a) {
		File file = new File("D:\\excel.xlsx");
		String[] columns = { "userName", "date", "dates", "index", "integerColumn", "test", "boolType", "longType" };
		try {
			FileInputStream fileInputStream = new FileInputStream(file);
			
			ImportExcel<Bean> excel = new ImportExcel<>();
			excel.setExcelStream(fileInputStream);
			excel.setBeanType(Bean.class);
			excel.setColumns(columns);
			excel.setXlsx2007(true);
			
			List<Bean> beanList = excel.toList();
			System.out.println(beanList);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		
	}
}

